var fs=require('fs');
var PostgreSQL=require('pgsql').PostgreSQL;
//var sys=require('sys');

/*****************************************************************************/
OneWorldCmsSql=function(config,paths) {
	this.paths=paths;
	this.prepared_query={};
	this.prepared_array={};
	this.href={};
	this.config=config;
	this.connect(this.config);
	this.was_connected=1;
	return this;
}

/*****************************************************************************/
OneWorldCmsSql.prototype.setHref=function(href) {
	this.href=href;
}

/*****************************************************************************/
OneWorldCmsSql.prototype.load=function(filename) {
	var i=0;
	var pathes="";
	while (i<this.paths.length) {
		var path=this.paths[i]+filename+".sql";
		try {
			var f=new fs.File(path);
			f.open('r');
			var contents=f.read();
			f.close();
			return contents.toString("utf-8");
		} catch(e) {
			if (pathes.length) pathes+=", ";
			pathes+=path;
			i++;
			continue;
		}
	}
	throw("SQL:load() - File not found - "+filename+"\nSQL: searched "+pathes+"\n");
}

/*****************************************************************************/
OneWorldCmsSql.prototype.connect=function(database) {
	var t=this;
	this.autocommit=false;
	switch (database.driver||'pg') {
		case 'pg':
			var url="";
			url+="host="+(database.host||'127.0.0.1');
			url+=" port="+(database.port||5432);
			url+=" dbname="+database.database;
			url+=" user="+database.login;
			url+=" password="+database.password;
			this.dbh=new PostgreSQL(url);
//			this.dbh.queryParams("set autocommit=off",[]);
			break;
	}
}

/*****************************************************************************/
OneWorldCmsSql.prototype.auto_start_transaction=function()
{
	if (this.tr_started || this.autocommit) return;
	this.start_transaction();
}

/*****************************************************************************/
OneWorldCmsSql.prototype.start_transaction=function()
{
	if (this.tr_started) return;
	try {
		this.dbh.queryParams("begin",[]);
		this.tr_started=true;
	} catch (e) {
		throw("SQL:start_transaction() - "+e);
	}
}

/*****************************************************************************/
OneWorldCmsSql.prototype.prepare=function(text,filename) {
	var arr=[];
	var hash={};
	var a;
	var i=0;
	var src=text;
	while (a=text.match(/([^:\w]):([a-z_0-9]+(?:\.\w+)*)/)) {
		var pre=a[1];
		var varname=a[2];
		var pos;
		if (hash[varname]) {
			pos=hash[varname];
		} else {
			arr.push(varname.split("."));
			pos=arr.length;
			hash[varname]=pos;
		}
		text=text.replace(/[^:\w]:[a-z_0-9]+(?:\.\w+)*/,pre+"$"+pos);
	}
//	this.prepared_query[filename]=this.dbh.prepare(filename,text);
	this.prepared_query[filename]=text;
	this.prepared_array[filename]=arr;
}

/*****************************************************************************/
OneWorldCmsSql.prototype.last_insert_id=function(tablename) {
	return this.execute_helper("select currval(pg_get_serial_sequence(:table,'id')) as lastid",{table: tablename.toLowerCase()},1,1).lastid;
}

/*****************************************************************************/
OneWorldCmsSql.prototype.bind=function(file,href,fields) {
	try {
		var arr=[];
		var parr=this.prepared_array[file];
		var thref=fields||this.href;
		for (var i=0;i<parr.length;i++) {
			var path=parr[i];
			var value=undefined;
			if (href) value=href[path[0]];
			if (value===undefined && href && href.fields) value=href.fields[path[0]];
			if (value===undefined && thref) value=thref[path[0]];
			if (value===undefined && thref.fields) value=thref.fields[path[0]];
			var j=1;
			while (value!=null && value!=undefined && j<path.length) {
				value=value[path[j]];
				j++;
			}
			arr.push(value);
		}
		return arr;
	} catch (e) {
		this.rollback();
		throw("SQL:bind("+file+"): "+e);
	}
}

/*****************************************************************************/
OneWorldCmsSql.prototype.rollback=function()
{
	if (!this.tr_started) return;
	return;
	try {
		this.dbh.queryParams("rollback",[]);
		this.tr_started=false;
	} catch (e) {
		throw("SQL:rollback - "+e);
	}
}

/*****************************************************************************/
OneWorldCmsSql.prototype.commit=function()
{
	if (!this.tr_started) return;
	try {
		this.dbh.queryParams("commit",[]);
		this.tr_started=false;
	} catch (e) {
		throw("SQL:commit - "+e);
	}
}

/*****************************************************************************/
OneWorldCmsSql.prototype.execute_helper=function(file,href,is_single,cnt,fields)
{
	if (!this.dbh.isConnected() && this.was_connected) this.connect(this.config);
	try {
		if (!this.prepared_query[file]) this.prepare(is_single?file:this.load(file),file);
		var values=this.bind(file,href,fields);
		var sqlq=this.prepared_query[file];
		if (href && href!=this.href && href!=this.href.fields) {
			if (href.REPLACE) {
				for (var k in href.REPLACE) sqlq=sqlq.replace("/*"+k+"*/",href.REPLACE[k]);
			}
			if (href.FOOTER) sqlq+=href.FOOTER;
		}
		if (href && href.BIND_ADD) {
			// TODO forgot how to add array to array
			href.BIND_ADD.forEach(function(x) {values.push(x);});
		}
		this.auto_start_transaction();
		var res=this.dbh.queryParams(sqlq,values);
		var ret=res.fetchAllObjects();
		if (cnt==1) return ret?ret[0]:undefined;
		return ret;
	} catch(e) {
		this.rollback();
		throw("SQL:execute_helper("+file+") - "+e+",\nbind='"+(values?values.join("','"):"")+"'\nquery:\n"+sqlq+"\n");
	}
}

/*****************************************************************************/
OneWorldCmsSql.prototype.execute=function(file,href,fields) {
	return this.execute_helper(file,href,0,0,fields);
}

/*****************************************************************************/
OneWorldCmsSql.prototype.execute_and_fetch_one=function(file,href,fields)
{
	return this.execute_helper(file,href,0,1,fields);
}

/*****************************************************************************/
OneWorldCmsSql.prototype.execute_and_fetch=function(file,href,fields)
{
	return this.execute_helper(file,href,0,-1,fields);
}

/*****************************************************************************/
OneWorldCmsSql.prototype.execute_single=function(file,href,fields)
{
	return this.execute_helper(file,href,1,0,fields);
}

/*****************************************************************************/
OneWorldCmsSql.prototype.execute_and_fetch_one_single=function(file,href,fields)
{
	return this.execute_helper(file,href,1,1,fields);
}

/*****************************************************************************/
OneWorldCmsSql.prototype.execute_and_fetch_single=function(file,href,fields)
{
	return this.execute_helper(file,href,1,-1,fields);
}

/*****************************************************************************/
OneWorldCmsSql.prototype.execute_and_fetch_h=function(file,href,fields)
{
	if (!this.prepared_query[file]) this.prepare(this.load(file),file);
	return this.execute_and_fetch_h2(file,href,0,fields);
}

/*****************************************************************************/
OneWorldCmsSql.prototype.execute_and_fetch_h_single=function(file,href,fields)
{
	if (!this.prepared_query[file]) this.prepare(file,file);
	return this.execute_and_fetch_h2(file,href,0,fields);
}

/*****************************************************************************/
OneWorldCmsSql.prototype.execute_and_fetch_ha=function(file,href,fields)
{
	if (!this.prepared_query[file]) this.prepare(this.load(file),file);
	return this.execute_and_fetch_h2(file,href,1,fields);
}

/*****************************************************************************/
OneWorldCmsSql.prototype.execute_and_fetch_ha_single=function(file,href,fields)
{
	if (!this.prepared_query[file]) this.prepare(file,file);
	return this.execute_and_fetch_h2(file,href,1,fields);
}


/*****************************************************************************/
OneWorldCmsSql.prototype.execute_and_fetch_h2=function(file,href,use_array,fields)
{
	if (!href) href={};
	var parent_id_pos;
	var parr=this.prepared_array[file];
	for (var i=0;i<parr.length;i++) if (parr[i]=='parent_id') parent_id_pos=i;
	if (parent_id_pos==undefined) throw("SQL:execute_and_fetch_h() - parent_id_pos==undefined");
	var values=this.bind(file,href,fields);
	var arr=[];
	this.execute_and_fetch_h3(this.prepared_query[file],values,parent_id_pos,href.parent_id||0,1,arr,use_array,fields);
	return arr;
}

/*****************************************************************************/
OneWorldCmsSql.prototype.execute_and_fetch_h3=function(txt,values,parent_id_pos,parent_id,level,arr,use_array,fields)
{
	values[parent_id_pos]=parent_id;
	this.auto_start_transaction();
	var res=this.dbh.queryParams(txt,values);
//	throw("txt="+txt+", values="+values);
//	var cnt=res.numRows();
	var arr2=res.fetchAllObjects();
	for (var i=0;i<arr2.length;i++) {
		var ret=arr2[i];
		ret.level=level;
		arr.push(ret);
		if (use_array) {
			var children=[];
			this.execute_and_fetch_h3(txt,values,parent_id_pos,ret.id,level+1,children,use_array);
			if (children.length) ret.children=children;
		} else {
			this.execute_and_fetch_h3(txt,values,parent_id_pos,ret.id,level+1,arr,use_array);
		}
	}
}

/*****************************************************************************/
exports.connect=function(config,paths)
{
	return new OneWorldCmsSql(config,paths);
}

/*****************************************************************************/
